﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Itau.SC.EnterpriseLib.Data;
using System.Data;
using System.Data.Odbc;
using System.Data.Common;
using System.Transactions;

namespace EnterpriseLibraryWrapper
{
    public class DataAccessWrapper : IDataAccessWrapper
    {

        #region Atributos

        Database _db;
        DbTransaction _transaction;
        DbConnection _connection;

        #endregion

        #region Propriedades

        public IList<ParameterInWrapper> ParameterIn
        {
            get;
            set;
        }

        public IList<ParameterOutWrapper> ParameterOut
        {
            get;
            set;
        }

        public string Ambiente
        {
            get;
            set;
        }

        #endregion

        #region Construtor

        public DataAccessWrapper()
        {
            this.ParameterIn = new List<ParameterInWrapper>();
            this.ParameterOut = new List<ParameterOutWrapper>();
            this.Ambiente = string.Empty;
        }

        #endregion

        #region Destrutor

        ~DataAccessWrapper()
        {
            Limpar();
        }

        #endregion

        #region Métodos

        public void BeginTransaction()
        {
            CreateDataBase();

            _connection = _db.CreateConnection();
            _connection.Open();
            _transaction = _connection.BeginTransaction();
        }

        private void CreateDataBase()
        {
            if (Ambiente == string.Empty)
            {
                _db = DatabaseFactory.CreateDatabase(System.Configuration.ConfigurationSettings.AppSettings["DB2_CONNECTION"]);
            }
            else
            {
                _db = DatabaseFactory.CreateDatabase(Ambiente);
            }
        }

        public void CommitTransaction()
        {
            _transaction.Commit();
        }

        public void RollBackTransaction()
        {
            _transaction.Rollback();
        }

        public void CloseConnection()
        {
            if (_connection != null && _connection.State != ConnectionState.Closed)
            {
                _connection.Close();
            }
        }

        public DataSet ExecuteDataSet(string storedProcedureName)
        {
            if (_db == null)
            {
                CreateDataBase();
                _connection = _db.CreateConnection();
                _connection.Open();
            }

            DataSet resultado;
            using (DbCommand _dbCommand = _db.GetStoredProcCommand(storedProcedureName))
            {

                foreach (ParameterInWrapper parameterIn in ParameterIn)
                {
                    _db.AddInParameter(_dbCommand, parameterIn.ParameterName, parameterIn.ParameterType, parameterIn.ParameterValue);
                }

                foreach (ParameterOutWrapper parameterOut in ParameterOut)
                {
                    _db.AddOutParameter(_dbCommand, parameterOut.ParameterName, parameterOut.ParameterType, parameterOut.ParameterSize);
                }

                if (_transaction != null)
                    resultado = _db.ExecuteDataSet(_dbCommand, _transaction);
                else
                    resultado = _db.ExecuteDataSet(_dbCommand);

                return resultado;
            }
        }

        public int ExecuteNonQuery(string storedProcedureName)
        {
            if (_db == null)
            {
                CreateDataBase();
                _connection = _db.CreateConnection();
                _connection.Open();
            }

            int resultado = 0;
            using (DbCommand _dbCommand = _db.GetStoredProcCommand(storedProcedureName))
            {

                foreach (ParameterInWrapper parameterIn in ParameterIn)
                {
                    _db.AddInParameter(_dbCommand, parameterIn.ParameterName, parameterIn.ParameterType, parameterIn.ParameterValue);
                }

                foreach (ParameterOutWrapper parameterOut in ParameterOut)
                {
                    _db.AddOutParameter(_dbCommand, parameterOut.ParameterName, parameterOut.ParameterType, parameterOut.ParameterSize);
                }

                if (_transaction != null)
                    resultado = _db.ExecuteNonQuery(_dbCommand, _transaction);
                else
                    resultado = _db.ExecuteNonQuery(_dbCommand);

                foreach (ParameterOutWrapper parameterOut in ParameterOut)
                {
                    parameterOut.ParameterValue = _dbCommand.Parameters[parameterOut.ParameterName].Value;
                }

                return resultado;
            }
        }

        public object ExecuteScalar(string storedProcedureName)
        {
            if (_db == null)
            {
                CreateDataBase();
                _connection = _db.CreateConnection();
                _connection.Open();
            }

            object resultado;
            using (DbCommand _dbCommand = _db.GetStoredProcCommand(storedProcedureName))
            {

                foreach (ParameterInWrapper parameterIn in ParameterIn)
                {
                    _db.AddInParameter(_dbCommand, parameterIn.ParameterName, parameterIn.ParameterType, parameterIn.ParameterValue);
                }

                foreach (ParameterOutWrapper parameterOut in ParameterOut)
                {
                    _db.AddOutParameter(_dbCommand, parameterOut.ParameterName, parameterOut.ParameterType, parameterOut.ParameterSize);
                }

                if (_transaction != null)
                    resultado = _db.ExecuteScalar(_dbCommand, _transaction);
                else
                    resultado = _db.ExecuteScalar(_dbCommand);

                return resultado;
            }
        }

        public IDataReader ExecuteReader(string storedProcedureName)
        {
            if (_db == null)
            {
                CreateDataBase();
                _connection = _db.CreateConnection();
                _connection.Open();
            }

            IDataReader resultado;
            using (DbCommand _dbCommand = _db.GetStoredProcCommand(storedProcedureName))
            {

                foreach (ParameterInWrapper parameterIn in ParameterIn)
                {
                    _db.AddInParameter(_dbCommand, parameterIn.ParameterName, parameterIn.ParameterType, parameterIn.ParameterValue);
                }

                foreach (ParameterOutWrapper parameterOut in ParameterOut)
                {
                    _db.AddOutParameter(_dbCommand, parameterOut.ParameterName, parameterOut.ParameterType, parameterOut.ParameterSize);
                }

                if (_transaction != null)
                    resultado = _db.ExecuteReader(_dbCommand, _transaction);
                else
                    resultado = _db.ExecuteReader(_dbCommand);

                return resultado;
            }
        }

        public DataSet ExecuteParameterToDataSet(string storedProcedureName)
        {
            if (_db == null)
            {
                CreateDataBase();
                _connection = _db.CreateConnection();
                _connection.Open();
            }

            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            DataRow dr = dt.NewRow();
            bool _existeRegistro = false;
            using (DbCommand _dbCommand = _db.GetStoredProcCommand(storedProcedureName))
            {

                foreach (ParameterInWrapper parameterIn in ParameterIn)
                {
                    _db.AddInParameter(_dbCommand, parameterIn.ParameterName, parameterIn.ParameterType, parameterIn.ParameterValue);
                    dt.Columns.Add(RemoverArrouba(parameterIn.ParameterName.ToString()), ConvertType(parameterIn.ParameterType));
                    dr[RemoverArrouba(parameterIn.ParameterName)] = _dbCommand.Parameters[parameterIn.ParameterName].Value;
                }

                foreach (ParameterOutWrapper parameterOut in ParameterOut)
                {
                    _db.AddOutParameter(_dbCommand, parameterOut.ParameterName, parameterOut.ParameterType, parameterOut.ParameterSize);
                    dt.Columns.Add(RemoverArrouba(parameterOut.ParameterName.ToString()), ConvertType(parameterOut.ParameterType));
                }

                if (_transaction != null)
                    _db.ExecuteNonQuery(_dbCommand, _transaction);
                else
                    _db.ExecuteNonQuery(_dbCommand);

                foreach (ParameterInWrapper parameterIn in ParameterIn)
                {
                    dr[RemoverArrouba(parameterIn.ParameterName)] = _dbCommand.Parameters[parameterIn.ParameterName].Value;
                }

                foreach (ParameterOutWrapper parameterOut in ParameterOut)
                {
                    if (_dbCommand.Parameters[parameterOut.ParameterName].Value != DBNull.Value)
                    {
                        _existeRegistro = true;
                    }

                    dr[RemoverArrouba(parameterOut.ParameterName)] = _dbCommand.Parameters[parameterOut.ParameterName].Value;
                }

                if (_existeRegistro)
                {
                    dt.Rows.Add(dr);
                }

                ds.Tables.Add(dt);

                return ds;
            }
        }

        private string RemoverArrouba(string nomeParametro)
        {
            return nomeParametro.Replace("@", string.Empty);
        }

        private Type ConvertType(DbType dbType)
        {
            Type toReturn = typeof(DBNull);

            switch (dbType)
            {
                case DbType.String:
                    toReturn = typeof(string);
                    break;

                case DbType.UInt64:
                    toReturn = typeof(UInt64);
                    break;

                case DbType.Int64:
                    toReturn = typeof(Int64);
                    break;

                case DbType.Int32:
                    toReturn = typeof(Int32);
                    break;

                case DbType.UInt32:
                    toReturn = typeof(UInt32);
                    break;

                case DbType.Single:
                    toReturn = typeof(float);
                    break;

                case DbType.Date:
                    toReturn = typeof(DateTime);
                    break;

                case DbType.DateTime:
                    toReturn = typeof(DateTime);
                    break;

                case DbType.Time:
                    toReturn = typeof(TimeSpan);
                    break;

                case DbType.StringFixedLength:
                    toReturn = typeof(string);
                    break;

                case DbType.UInt16:
                    toReturn = typeof(UInt16);
                    break;

                case DbType.Int16:
                    toReturn = typeof(Int16);
                    break;

                case DbType.SByte:
                    toReturn = typeof(byte);
                    break;

                case DbType.Object:
                    toReturn = typeof(object);
                    break;

                case DbType.AnsiString:
                    toReturn = typeof(string);
                    break;

                case DbType.AnsiStringFixedLength:
                    toReturn = typeof(string);
                    break;

                case DbType.VarNumeric:
                    toReturn = typeof(decimal);
                    break;

                case DbType.Currency:
                    toReturn = typeof(double);
                    break;

                case DbType.Binary:
                    toReturn = typeof(byte[]);
                    break;

                case DbType.Decimal:
                    toReturn = typeof(decimal);
                    break;

                case DbType.Double:
                    toReturn = typeof(Double);
                    break;

                case DbType.Guid:
                    toReturn = typeof(Guid);
                    break;

                case DbType.Boolean:
                    toReturn = typeof(bool);
                    break;
            }

            return toReturn;
        }

        void IDisposable.Dispose()
        {
            Limpar();
            GC.SuppressFinalize(this);
        }

        private void Limpar()
        {
            CloseConnection();

            if (_connection != null && _connection.State != ConnectionState.Closed)
            {
                _connection.Close();
            }

            if (_db != null)
            {
                _db = null;
            }

            if (this.ParameterIn != null)
            {
                this.ParameterIn.Clear();
            }

            if (this.ParameterOut != null)
            {
                this.ParameterOut.Clear();
            }

            if (_connection != null)
            {
                _connection.Dispose();
                _connection = null;
            }

            if (_transaction != null)
            {
                _transaction.Dispose();
                _transaction = null;
            }
        }

        #endregion

    }
}
